import os
import shutil
from collections import defaultdict
from datetime import datetime
from urllib.parse import quote

import pandas as pd
import xlrd
from openpyxl.reader.excel import load_workbook
from sqlalchemy.dialects.mysql import BIGINT
import logging
from flask import request, jsonify, url_for, send_file
from flask_restx import Namespace, Resource, fields
from flask_jwt_extended import jwt_required
from sqlalchemy import and_, func, or_

import app1

# 导入模型
from app1.models import KmsAppInfo, StaffInfo, IntegratedTask, IntegratedTaskFileAttribute, IntegratedTaskConfig
from app1 import db, app
from app1 import api

from app1.customdate import CustomDate
from app1.utils import format_response, check_permission, check_simple_roles

from app1.utils import format_response, format_response_page

# 获取路径
YTH_UPLOAD_FOLDER = app1.app.config['YTH_UPLOAD_FOLDER']
YTH_UPLOAD_COPY_FOLDER = app1.app.config['YTH_UPLOAD_COPY_FOLDER']
YTH_DOWNLOAD_FOLDER = app1.app.config['YTH_DOWNLOAD_FOLDER']
YTH_EXCEL_TEMPLATE = app1.app.config['YTH_EXCEL_TEMPLATE']

# 角色权限控制
YTH_ROLES = [2]

# 确保上传和复制的目录存在
os.makedirs(YTH_UPLOAD_FOLDER, exist_ok=True)
os.makedirs(YTH_UPLOAD_COPY_FOLDER, exist_ok=True)

# 创建 Flask-RESTx 命名空间
ns_yth = Namespace('ns_yth', description='一体化运维')

# ================= model区域 ======================
# kms_app
# 定义 Flask-RESTx 模型
kms_app_info_model = ns_yth.model('KmsAppInfo', {
    'service_catalog': fields.String(description='服务目录编号', required=True),
    'service_name': fields.String(description='服务名', required=True),
    'en_short_name': fields.String(description='英文简称', required=True),
    'alarm_alias': fields.String(description='告警别名', required=True),
    'role_a': fields.String(description='A角', required=True),
    'role_b': fields.String(description='B角', required=True),
    'ha_grade': fields.String(description='可用性级别', required=True),
    'dr_grade': fields.String(description='灾备级别', required=True),
    'service_time': fields.String(description='服务时间', required=True),
    'system_platform': fields.String(description='系统平台', required=True),
    'dev_org': fields.String(description='开发单位', required=True),
    'importance_grade': fields.String(description='重要性分级', required=True),
    'developer': fields.String(description='开发项目组', required=False, default=None),
})

# staff

# 定义 Flask-RESTx 模型
staff_info_model = ns_yth.model('StaffInfo', {
    'id': fields.Integer(description='Serial Number', required=False),
    'name': fields.String(description='Name', required=True),
    'gender': fields.String(description='Gender', required=True),
    'employee_id': fields.String(description='Employee ID', required=True),
    'id_card': fields.String(description='Identity Card Number', required=True),
    'contact': fields.String(description='Contact Information', required=True),
    'dev_dept': fields.String(description='Development Department', required=True),
    'team': fields.String(description='Exchange Team', required=True),
    'duration': fields.String(description='Exchange Duration', required=True),
    'start_date': CustomDate(dt_format='str_time', description='Exchange Start Date', required=True),
    'end_date': CustomDate(dt_format='str_time', description='Exchange End Date', required=True),
    'expert': fields.String(description='Product Expert', required=True),
    'maintenance': fields.String(description='Maintenance Personnel', required=True),
})

# integrated_task


# 定义 Flask-RESTx 模型
integrated_task_model = ns_yth.model('IntegratedTask', {
    'id': fields.Integer(description='Primary Key ID', required=False),
    'integrated_work_id': fields.Integer(description='Integrated Work Table ID', required=True),
    'work_category': fields.String(description='Work Category', required=True),
    'work_subcategory': fields.String(description='Work Subcategory', required=True),
    'work_item': fields.String(description='Work Item', required=True),
    'work_task': fields.String(description='Work Task', required=True),
    'working_hours': fields.String(description='Working Hours', required=True),
    'actual_work_content': fields.String(description='Actual Work Content', required=False),
    'remarks': fields.String(description='Remarks', required=False),
    'create_time': CustomDate(dt_format='str_time', description='Creation Time'),
    'update_time': CustomDate(dt_format='str_time', description='Update Time'),
})

# integrated_task_file_attribute

# 定义 Flask-RESTx 模型
integrated_task_file_attribute_model = ns_yth.model('IntegratedTaskFileAttribute', {
    'id': fields.Integer(description='唯一标识', readonly=True),
    'integrated_work_id': fields.Integer(required=True, description='一体化工作表ID'),
    'file_name': fields.String(required=True, description='一体化工作表文件名'),
    'app_abbreviation': fields.String(required=True, description='应用简称'),
    'dev_department': fields.String(required=True, description='开发部门'),
    'exchange_employee_id': fields.String(required=True, description='交流员工号'),
    'exchange_employee_name': fields.String(required=True, description='交流员工名字'),
    'work_period': fields.String(required=True, description='一体化工作期数'),
    'create_time': CustomDate(dt_format='str_time', description='Creation Time'),
    'update_time': CustomDate(dt_format='str_time', description='Update Time'),
})

# 定义 Flask-RESTx 模型
integrated_task_config_model = ns_yth.model('IntegratedTaskConfig', {
    'id': fields.Integer(description='Primary Key ID', required=False),
    'work_category': fields.String(description='Work Category', required=True),
    'work_subcategory': fields.String(description='Work Subcategory', required=True),
    'work_item': fields.String(description='Work Item', required=True),
    'remarks': fields.String(description='Remarks', required=False),
    'create_time': CustomDate(dt_format='str_time', description='Creation Time'),
    'update_time': CustomDate(dt_format='str_time', description='Update Time'),
})

# 对应关系
# work_category 对应 firstTitle
# work_subcategory 对应 secondTitle
# work_item 对应 taskItem
# work_task 对应 job
# working_hours 对应 time
# actual_work_content 对应 taskContent
# remarks 对应 comment

Excel_Task_content_model = ns_yth.model('Excel_Task_content', {
    'taskItem': fields.String(description='taskItem', required=True),
    'job': fields.String(description='job', required=True),
    'time': fields.String(description='data', required=True),
    'taskContent': fields.String(description='data', required=True),
    'comment': fields.String(description='data', required=True)
})

Excel_Task_data_model = ns_yth.model('Excel_Task_data', {
    'secondTitle': fields.String(description='secondTitle', required=True),
    'content': fields.List(fields.Nested(Excel_Task_content_model))
})

# 定义 Flask-RESTx 模型
Excel_Task_model = ns_yth.model('Excel_Task', {
    'firstTitle': fields.String(description='firstTitle', required=True),
    'data': fields.List(fields.Nested(Excel_Task_data_model))
})

# data no array
# 定义 Flask-RESTx 模型
# 定义 content 数据的结构
Excel_Task_content1_model = ns_yth.model('Excel_Task_Content', {
    'id': fields.String(description='Primary Key ID', required=False),
    'taskItem': fields.String(description='任务项描述', required=True),
    'job': fields.String(description='具体工作内容', required=True),
    'time': fields.String(description='执行时间', required=True),
    'taskContent': fields.String(description='任务详细内容', required=True),
    'comment': fields.String(description='备注信息', required=False),
})

# 定义 data1 的结构
Excel_Task_data1_model = ns_yth.model('Excel_Task_Data', {
    'secondTitle': fields.String(description='二级标题', required=True),
    'content': fields.List(fields.Nested(Excel_Task_content1_model), description='任务内容列表', required=True),
})

# 定义最终输出的结构
Excel_Task_Noarray_model = ns_yth.model('Excel_Task_Noarray', {
    'firstTitle': fields.String(description='一级标题', required=True),
    'data': fields.Nested(Excel_Task_data1_model, description='与一级标题对应的任务数据', required=True),
})

# ================= 输入区域 ======================
# kms_app
kms_app_multi_query_group_model = ns_yth.model(
    'kms_app_info_multi_query_group',
    {
        'service_catalog': fields.List(fields.String, description='服务目录编号', default=[]),
        'service_name': fields.List(fields.String, description='服务名', default=[]),
        'en_short_name': fields.List(fields.String, description='英文简称', default=[]),
        'alarm_alias': fields.List(fields.String, description='告警别名', default=[]),
        'role_a': fields.List(fields.String, description='A角', default=[]),
        'role_b': fields.List(fields.String, description='B角', default=[]),
        'ha_grade': fields.List(fields.String, description='可用性级别', default=[]),
        'dr_grade': fields.List(fields.String, description='灾备级别', default=[]),
        'service_time': fields.List(fields.String, description='服务时间', default=[]),
        'system_platform': fields.List(fields.String, description='系统平台', default=[]),
        'dev_org': fields.List(fields.String, description='开发单位', default=[]),
        'importance_grade': fields.List(fields.String, description='重要性分级', default=[]),
        'developer': fields.List(fields.String, description='开发项目组', default=[])
    }
)

# staff
kms_staff_query_group_model = ns_yth.model(
    'kms_staff_multi_query_group',
    {
        'id': fields.List(fields.Integer, description='Serial Number', default=[]),
        'name': fields.List(fields.String, description='Name', default=[]),
        'gender': fields.List(fields.String, description='Gender', default=[]),
        'employee_id': fields.List(fields.String, description='Employee ID', default=[]),
        'id_card': fields.List(fields.String, description='Identity Card Number', default=[]),
        'contact': fields.List(fields.String, description='Contact Information', default=[]),
        'dev_dept': fields.List(fields.String, description='Development Department', default=[]),
        'team': fields.List(fields.String, description='Exchange Team', default=[]),
        'duration': fields.List(fields.String, description='Exchange Duration', default=[]),
        'StartDate_start_date': fields.String(description='开始日期起始',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'StartDate_end_date': fields.String(description='开始日期结束',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_start_date': fields.String(description='结束日期开始',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_end_date': fields.String(description='结束日期结束',
                                          default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'expert': fields.List(fields.String, description='Product Expert', default=[]),
        'maintenance': fields.List(fields.String, description='Maintenance Personnel', default=[]),  # 模糊查询
    }
)

# integrated_task

kms_itg_task_query_group_model = ns_yth.model(
    'kms_itg_task_multi_query_group',
    {
        'id': fields.List(fields.Integer, description='Primary Key ID', default=[]),
        'integrated_work_id': fields.List(fields.Integer, description='Integrated Work Table ID', default=[]),
        'work_category': fields.List(fields.String, description='Work Category', default=[]),
        'work_subcategory': fields.List(fields.String, description='Work Subcategory', default=[]),
        'work_item': fields.List(fields.String, description='Work Item', default=[]),
        'work_task': fields.List(fields.String, description='Work Task', default=[]),
        'working_hours': fields.List(fields.String, description='Working Hours', default=[]),
        'actual_work_content': fields.List(fields.String, description='Actual Work Content', default=[]),
        'remarks': fields.List(fields.String, description='Remarks', default=[]),
        'create_time_start_date': fields.String(description='创建日期起始',
                                                default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'create_time_end_date': fields.String(description='创建日期结束',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time_start_date': fields.String(description='修改日期开始',
                                                default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time_end_date': fields.String(description='修改日期结束',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

kms_itg_task_query_model = ns_yth.model(
    'kms_itg_task_query',
    {
        'id': fields.String(description='Primary Key ID', default=''),
        'integrated_work_id': fields.String(description='Integrated Work Table ID', default=''),
        'work_category': fields.String(description='Work Category', default=''),
        'work_subcategory': fields.String(description='Work Subcategory', default=''),
        'work_item': fields.String(description='Work Item', default=''),
        'work_task': fields.String(description='Work Task', default=''),
        'working_hours': fields.String(description='Working Hours', default=''),
        'actual_work_content': fields.String(description='Actual Work Content', default=''),
        'remarks': fields.String(description='Remarks', default=''),
        'create_time_start_date': fields.String(description='创建日期起始',
                                                default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'create_time_end_date': fields.String(description='创建日期结束',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time_start_date': fields.String(description='修改日期开始',
                                                default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time_end_date': fields.String(description='修改日期结束',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

kms_itg_task_crud_model = ns_yth.model(
    'kms_itg_task_crud',
    {
        'id': fields.String(description='Primary Key ID', default=''),
        'integrated_work_id': fields.String(description='Integrated Work Table ID', default=''),
        'work_category': fields.String(description='Work Category', default=''),
        'work_subcategory': fields.String(description='Work Subcategory', default=''),
        'work_item': fields.String(description='Work Item', default=''),
        'work_task': fields.String(description='Work Task', default=''),
        'working_hours': fields.String(description='Working Hours', default=''),
        'actual_work_content': fields.String(description='Actual Work Content', default=''),
        'remarks': fields.String(description='Remarks', default=''),
        'create_time': fields.String(description='Creation Time',
                                     default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time': fields.String(description='Update Time',
                                     default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

itg_task_config_qry_model = ns_yth.model(
    'itg_task_config_qry',
    {
        'work_category': fields.List(fields.String, description='工作类别', default=[]),
        'work_subcategory': fields.List(fields.String, description='工作细类', default=[]),
        'work_item': fields.List(fields.String, description='工作项', default=[]),
    }
)

kms_itg_task_del_model = ns_yth.model(
    'kms_itg_task_del',
    {
        'integrated_work_id': fields.String(description='Integrated Work Table ID', default=""),
    }
)

kms_itg_task_qry_model = ns_yth.model(
    'kms_itg_task_qry',
    {
        'integrated_work_id': fields.String(description='Integrated Work Table ID', default=""),
    }
)

# integrated_task_file_attribute
kms_itg_task_fa_crud_model = ns_yth.model(
    'kms_itg_task_fa_crud',
    {
        # 'id': fields.Integer(description='唯一标识', readonly=True),
        'integrated_work_id': fields.String(required=True, description='一体化工作表ID', default=""),
        'file_name': fields.String(required=True, description='一体化工作表文件名', default=""),
        'app_abbreviation': fields.String(required=True, description='应用简称', default=""),
        'dev_department': fields.String(required=True, description='开发部门', default=""),
        'exchange_employee_id': fields.String(required=True, description='交流员工号', default=""),
        'exchange_employee_name': fields.String(required=True, description='交流员工名字', default=""),
        'work_period': fields.String(required=True, description='一体化工作期数', default=""),
        'create_time': fields.String(description='Creation Time',
                                     default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time': fields.String(description='Update Time',
                                     default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

# integrated_task_file_attribute
kms_itg_task_fa_short_crud_model = ns_yth.model(
    'kms_itg_task_fa_short_crud',
    {
        'app_abbreviation': fields.String(required=True, description='应用简称', default=""),
        'dev_department': fields.String(required=True, description='开发部门', default=""),
        'exchange_employee_id': fields.String(required=True, description='交流员工号', default=""),
        'exchange_employee_name': fields.String(required=True, description='交流员工名字', default=""),
        'work_period': fields.String(required=True, description='一体化工作期数', default=""),
        'create_time': fields.String(description='Creation Time',
                                     default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'update_time': fields.String(description='Update Time',
                                     default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

# 定义查询请求参数模型
query_itgfa_task_model = ns_yth.model('QueryItgfaTask', {
    'id': fields.String(description='唯一标识', default=""),
    'integrated_work_id': fields.String(description='一体化工作表ID', default=""),
    'file_name': fields.String(description='一体化工作表文件名', default=""),
    'app_abbreviation': fields.String(description='应用简称', default=""),
    'dev_department': fields.String(description='开发部门', default=""),
    'exchange_employee_id': fields.String(description='交流员工号', default=""),
    'exchange_employee_name': fields.String(description='交流员工名字', default=""),
    'work_period': fields.String(required=True, description='一体化工作期数', default=""),
})

# 定义查询请求参数模型
del_itgfa_task_model = ns_yth.model('DelItgfaTask', {
    'integrated_work_id': fields.String(description='一体化工作表ID', default="")
})

# integrated_task_file_attribute
itg_task_fa_qry_group_model = ns_yth.model(
    'itg_task_fa_qry_group',
    {
        'integrated_work_id': fields.List(fields.String, required=True, description='一体化工作表ID', default=[]),
        'file_name': fields.List(fields.String, required=True, description='一体化工作表文件名', default=[]),
        'app_abbreviation': fields.List(fields.String, required=True, description='应用简称', default=[]),
        'dev_department': fields.List(fields.String, required=True, description='开发部门', default=[]),
        'exchange_employee_id': fields.List(fields.String, required=True, description='交流员工号', default=[]),
        'exchange_employee_name': fields.List(fields.String, required=True, description='交流员工名字', default=[]),
        'work_period': fields.List(fields.String, required=True, description='一体化工作期数', default=[]),
        'pageNo': fields.Integer(required=False, description='start page', default=1),
        'pageSize': fields.Integer(required=False, description='per page', default=20),
        'max_page': fields.Integer(required=False, description='max page', default=50),
    }
)

# 定义查询请求参数模型
list_itgfa_task_model = ns_yth.model('ListItgfaTask', {
    'integrated_work_id': fields.String(description='一体化工作表ID', default="")
})

# 定义删除请求参数模型
list_itgfa_task_del_model = ns_yth.model('ListItgfaTaskDel', {
    'integrated_work_id': fields.String(description='一体化工作表ID', default="")
})

# 定义一体化任务清单更新请求参数模型
list_itgfa_upd_task_model = ns_yth.model('ListItgfaUpdTask', {
    'integrated_work_id': fields.String(description='一体化工作表ID', default=""),
    'integrated_work_op': fields.String(description='操作类型', default=""),
    'integrated_work_data': fields.Raw(description='一体化工作表明细')
})

# 定义文件上传的模型
yth_upload_model = ns_yth.parser()
yth_upload_model.add_argument('file', location='files', type='FileStorage', required=True, help='文件上传')

# 定义需要读取文件列表的目录
yth_up_file_list_query_model = ns_yth.model(
    'yth_up_file_list_query',
    {
        'dir_name': fields.String(description='指定目录', required=True, default=YTH_UPLOAD_COPY_FOLDER),
    }
)

# 定义需要解析的文件列表的目录和文件名
yth_parse_dir_file_model = ns_yth.model(
    'yth_parse_dir_file',
    {
        'dir_name': fields.String(description='指定目录', required=True, default=YTH_UPLOAD_COPY_FOLDER),
        'excel_name': fields.String(description='文件名', required=True, default=""),
    }
)

# ================= 输出区域 ======================

# 定义文件上传响应的模型
yth_up_model = ns_yth.model('yth_upload_response_data', {
    'original_file_path': fields.String(description='上传文件路径'),
    'copied_file_path': fields.String(description='复制文件路径'),
    'download_url': fields.String(description='下载文件的 URL'),
})

response_yth_upload_model = ns_yth.model('Response_yth_upload', {
    'message': fields.Nested(ns_yth.model('Message_yth_upload', {
        "result": fields.Integer(description='操作结果'),
        "result_text": fields.String(description='结果描述'),
        "data": fields.List(fields.Nested(yth_up_model))
    }))
})

# 定义 Flask-RESTx 模型
yth_up_file_list_model = ns_yth.model('alarm_file_list', {
    'file_name': fields.String(description='文件名称'),
    'file_ctime': fields.String(description='文件修改日期'),
    'file_type': fields.String(description='文件类型'),
    'file_size': fields.String(description='文件大小')
})

# 定义外层的响应模型
response_yth_up_file_list_model = ns_yth.model('Response_yth_up_file_list', {
    'message': fields.Nested(ns_yth.model('Message_yth_up_file_list', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(yth_up_file_list_model))
    }))
})

# kms_app


# 定义外层的响应模型
response_kms_app_model = ns_yth.model('Response_Kms_App', {
    'message': fields.Nested(ns_yth.model('Message_Kms_App', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(kms_app_info_model))
    }))
})

# staff
# 定义外层的响应模型
response_staff_model = ns_yth.model('Response_Staff', {
    'message': fields.Nested(ns_yth.model('Message_Staff', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(staff_info_model))
    }))
})

# integrated_task


# 定义外层的响应模型
response_integrated_task_model = ns_yth.model('Response_Integrated_Task', {
    'message': fields.Nested(ns_yth.model('Message_Integrated_Task', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(integrated_task_model))
    }))
})

# 定义一体化配置文件外层的响应模型
response_integrated_task_config_model = ns_yth.model('Response_Integrated_Task_Config', {
    'message': fields.Nested(ns_yth.model('Message_Integrated_Task_Config', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(integrated_task_config_model))
    }))
})

# integrated_task_file_attribute
# 定义外层的响应模型
response_integrated_task_fa_model = ns_yth.model('Response_Integrated_Task_Fa', {
    'message': fields.Nested(ns_yth.model('Message_Integrated_Task_Fa', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(integrated_task_file_attribute_model))
    }))
})

itg_task_fa_page_data_model = ns_yth.model('itg_task_fa_pagedata', {
    'has_next': fields.Boolean(),  # has_next: 是否还有下一页
    'has_prev': fields.Boolean(),  # has_prev: 是否还有上一页
    'data': fields.List(fields.Nested(integrated_task_file_attribute_model)),  # items: 返回当前页的所有内容
    'next': fields.List(fields.Nested(integrated_task_file_attribute_model)),
    # next(error_out=False): 返回下一页的Pagination对象
    'prev': fields.List(fields.Nested(integrated_task_file_attribute_model)),
    # prev(error_out=False): 返回上一页的Pagination对象
    'pageNo': fields.Integer(),  # page: 当前页的页码(从1开始)
    'totalPage': fields.Integer(),  # pages: 总页数
    'pageSize': fields.Integer(),  # per_page: 每页显示的数量
    'prev_num': fields.Integer(),  # prev_num: 上一页页码数
    'next_num': fields.Integer(),  # next_num: 下一页页码数
    # query: 返回
    'totalCount': fields.Integer()  # total: 查询返回的记录总数
})

response_itg_task_fa_data_model = ns_yth.model('Response_itg_task_fa_page_data', {
    "message": fields.String(description='翻页查询结果'),
    "result": fields.Nested(itg_task_fa_page_data_model),
    "status": fields.Integer(description='返回状态', default=200),  # 查询返回状态,
    "timestamp": fields.String(description='时间戳', default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),  # 时间戳
})

# staff
# 定义外层的响应模型
Respons_Excel_Task_model = ns_yth.model('Response_Excel_Task', {
    'message': fields.Nested(ns_yth.model('Message_Excel_Task', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(staff_info_model))
    }))
})

# 一体化excel 输出
response_integrated_task_list_model = ns_yth.model('Response_Integrated_Task_List', {
    'message': fields.Nested(ns_yth.model('Message_Integrated_Task_List', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(Excel_Task_model))
    }))
})

# 一体化excel data非数组输出
response_integrated_task_list_noarray_model = ns_yth.model('Response_Integrated_Task_List_Noarray', {
    'message': fields.Nested(ns_yth.model('Message_Integrated_Task_List_Noarray', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(Excel_Task_Noarray_model))
    }))
})


# ================= 类 ===========================
# kms_app

# 根据给定多条件查询应用信息并给出明细
@ns_yth.route('/getKmsAppbyMultiGroupquerylist')
class KmsAppMultiGroupMapResource(Resource):

    @ns_yth.expect(kms_app_multi_query_group_model)
    @ns_yth.marshal_with(response_kms_app_model)
    def post(self):
        """根据给定多条件查询应用信息并给出明细"""
        try:
            # 获取请求的payload
            query_filters = []
            data = api.payload

            # 构建查询条件
            if data.get('service_catalog'):
                query_filters.append(KmsAppInfo.service_catalog.in_(data['service_catalog']))
            if data.get('service_name'):
                # 模糊查询 service_name
                query_filters.append(KmsAppInfo.service_name.like(f"%{data['service_name'][0]}%"))
            if data.get('en_short_name'):
                query_filters.append(KmsAppInfo.en_short_name.in_(data['en_short_name']))
            if data.get('alarm_alias'):
                query_filters.append(KmsAppInfo.alarm_alias.in_(data['alarm_alias']))
            if data.get('role_a'):
                query_filters.append(KmsAppInfo.role_a.in_(data['role_a']))
            if data.get('role_b'):
                query_filters.append(KmsAppInfo.role_b.in_(data['role_b']))
            if data.get('ha_grade'):
                query_filters.append(KmsAppInfo.ha_grade.in_(data['ha_grade']))
            if data.get('dr_grade'):
                query_filters.append(KmsAppInfo.dr_grade.in_(data['dr_grade']))
            if data.get('service_time'):
                query_filters.append(KmsAppInfo.service_time.in_(data['service_time']))
            if data.get('system_platform'):
                query_filters.append(KmsAppInfo.system_platform.in_(data['system_platform']))
            if data.get('dev_org'):
                query_filters.append(KmsAppInfo.dev_org.in_(data['dev_org']))
            if data.get('importance_grade'):
                query_filters.append(KmsAppInfo.importance_grade.in_(data['importance_grade']))
            if data.get('developer'):
                # 模糊查询 developer
                query_filters.append(KmsAppInfo.developer.like(f"%{data['developer'][0]}%"))

            # 执行查询，使用 filter 结合条件
            results = KmsAppInfo.query.filter(*query_filters).all()

            # 准备响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": [result.to_dict() for result in results]
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# staff

# 根据给定多条件查询人员信息并给出明细
@ns_yth.route('/getStaffbyMultiGroupquerylist')
class StaffMultiGroupMapResource(Resource):

    @ns_yth.expect(kms_staff_query_group_model)
    @ns_yth.marshal_with(response_staff_model)
    def post(self):
        """根据给定多条件查询人员信息并给出明细"""
        try:
            # 获取请求的payload
            query_filters = []
            data = api.payload

            # 构建查询条件
            if data.get('id'):
                query_filters.append(StaffInfo.id.in_(data['id']))
            if data.get('name'):
                query_filters.append(StaffInfo.name.in_(data['name']))
            if data.get('gender'):
                query_filters.append(StaffInfo.gender.in_(data['gender']))
            if data.get('employee_id'):
                query_filters.append(StaffInfo.employee_id.in_(data['employee_id']))
            if data.get('id_card'):
                query_filters.append(StaffInfo.id_card.in_(data['id_card']))
            if data.get('contact'):
                query_filters.append(StaffInfo.contact.in_(data['contact']))
            if data.get('dev_dept'):
                query_filters.append(StaffInfo.dev_dept.in_(data['dev_dept']))
            if data.get('team'):
                query_filters.append(StaffInfo.team.in_(data['team']))
            if data.get('duration'):
                query_filters.append(StaffInfo.duration.in_(data['duration']))

            # 日期范围查询
            if data.get('StartDate_start_date') and data.get('StartDate_end_date'):
                start_date_start = datetime.strptime(data['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                start_date_end = datetime.strptime(data['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(StaffInfo.start_date.between(start_date_start, start_date_end))

            if data.get('EndDate_start_date') and data.get('EndDate_end_date'):
                end_date_start = datetime.strptime(data['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                end_date_end = datetime.strptime(data['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(StaffInfo.end_date.between(end_date_start, end_date_end))

            # 模糊查询 maintenance
            if data.get('maintenance'):
                query_filters.append(or_(*[StaffInfo.maintenance.like(f"%{value}%") for value in data['maintenance']]))

            # 执行查询，使用 filter 结合条件
            results = StaffInfo.query.filter(*query_filters).all()

            # 准备响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": [result.to_dict() for result in results]
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# integrated_task

# 新增
@ns_yth.route('/getItgtaskadd')
class ItgtaskaddMapResource(Resource):
    @ns_yth.expect(kms_itg_task_crud_model)
    @ns_yth.marshal_with(response_integrated_task_model)
    def post(self):
        """新增一个一体化任务条目"""
        try:
            # 获取请求的payload
            data = api.payload

            # 创建一个新的任务记录
            new_task = IntegratedTask(
                integrated_work_id=data['integrated_work_id'],
                work_category=data['work_category'],
                work_subcategory=data['work_subcategory'],
                work_item=data['work_item'],
                work_task=data['work_task'],
                working_hours=data['working_hours'],
                actual_work_content=data.get('actual_work_content', ''),
                remarks=data.get('remarks', '')
            )

            # 保存到数据库
            db.session.add(new_task)
            db.session.commit()

            # 返回响应数据
            response_data = {
                "message": {
                    "result": 1,  # 新增成功
                    "result_text": "新增任务成功",
                    "count": 1,
                    "data": [new_task.to_dict()]
                }
            }

            return response_data, 201  # 返回创建成功的状态码

        except Exception as e:
            response_data = {
                "message": {
                    "result": 0,  # 发生异常
                    "result_text": f"新增任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 400  # 返回错误状态码


# 查询
@ns_yth.route('/getItgtask/<int:id>')
class ItgtaskQueryResource(Resource):

    @ns_yth.marshal_with(response_integrated_task_model)
    def get(self, id):
        """查询一体化任务条目"""
        try:
            task = IntegratedTask.query.get(id)
            if task:
                response_data = {
                    "message": {
                        "result": 1,  # 查询成功
                        "result_text": "查询成功",
                        "count": 1,
                        "data": [task.to_dict()]
                    }
                }
                return response_data, 200  # 返回查询成功的状态码
            else:
                response_data = {
                    "message": {
                        "result": 0,  # 未找到任务
                        "result_text": "任务未找到",
                        "count": 0,
                        "data": []
                    }
                }
                return response_data, 404  # 返回未找到状态码

        except Exception as e:
            response_data = {
                "message": {
                    "result": 0,  # 发生异常
                    "result_text": f"查询任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 400  # 返回错误状态码


@ns_yth.route('/getItgtaskpost')
class ItgtaskQueryResource(Resource):
    @ns_yth.expect(kms_itg_task_qry_model)
    @ns_yth.marshal_with(response_integrated_task_model)
    def post(self):
        """查询一体化任务条目post"""
        try:
            data = api.payload
            integrated_work_id = data.get('integrated_work_id')

            if not integrated_work_id:
                return {
                    "message": {
                        "result": 0,
                        "result_text": "缺少integrated_work_id",
                        "count": 0,
                        "data": []
                    }
                }, 400

            task = IntegratedTask.query.filter_by(integrated_work_id=integrated_work_id).first()
            if task:
                return {
                    "message": {
                        "result": 1,
                        "result_text": "查询成功",
                        "count": 1,
                        "data": [task.to_dict()]
                    }
                }, 200

            return {
                "message": {
                    "result": 0,
                    "result_text": "任务未找到",
                    "count": 0,
                    "data": []
                }
            }, 404

        except Exception as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": f"查询任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


# 更新
@ns_yth.route('/updateItgtask/<int:id>')
class ItgtaskUpdateResource(Resource):
    @ns_yth.expect(kms_itg_task_crud_model)
    @ns_yth.marshal_with(response_integrated_task_model)
    def put(self, id):
        """更新一体化任务条目"""
        try:
            data = api.payload

            # 查询现有任务记录
            task = IntegratedTask.query.get(id)
            if task:
                # 检查integrated_work_id是否与原值相同
                if data.get('integrated_work_id') != task.integrated_work_id:
                    return {
                        "message": {
                            "result": 0,
                            "result_text": "integrated_work_id不允许修改",
                            "count": 0,
                            "data": []
                        }
                    }, 400

                # 更新其他字段
                task.work_category = data.get('work_category', task.work_category)
                task.work_subcategory = data.get('work_subcategory', task.work_subcategory)
                task.work_item = data.get('work_item', task.work_item)
                task.work_task = data.get('work_task', task.work_task)
                task.working_hours = data.get('working_hours', task.working_hours)
                task.actual_work_content = data.get('actual_work_content', task.actual_work_content)
                task.remarks = data.get('remarks', task.remarks)

                task.update_time = datetime.utcnow()
                db.session.commit()

                return {
                    "message": {
                        "result": 1,
                        "result_text": "更新任务成功",
                        "count": 1,
                        "data": [task.to_dict()]
                    }
                }, 200

            return {
                "message": {
                    "result": 0,
                    "result_text": "任务未找到",
                    "count": 0,
                    "data": []
                }
            }, 404

        except Exception as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": f"更新任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


@ns_yth.route('/updateItgtaskpost')
class ItgtaskUpdatepostResource(Resource):
    @ns_yth.expect(kms_itg_task_crud_model)
    @ns_yth.marshal_with(response_integrated_task_model)
    def post(self):
        """更新一体化任务条目post"""
        try:
            data = api.payload
            task_id = data.get('id')

            if not task_id:
                return {
                    "message": {
                        "result": 0,
                        "result_text": "缺少任务ID",
                        "count": 0,
                        "data": []
                    }
                }, 400

            task = IntegratedTask.query.get(task_id)
            if not task:
                return {
                    "message": {
                        "result": 0,
                        "result_text": "任务未找到",
                        "count": 0,
                        "data": []
                    }
                }, 404

            if data.get('integrated_work_id') != str(task.integrated_work_id):
                return {
                    "message": {
                        "result": 0,
                        "result_text": "integrated_work_id不允许修改",
                        "count": 0,
                        "data": []
                    }
                }, 400

            task.work_category = data.get('work_category', task.work_category)
            task.work_subcategory = data.get('work_subcategory', task.work_subcategory)
            task.work_item = data.get('work_item', task.work_item)
            task.work_task = data.get('work_task', task.work_task)
            task.working_hours = data.get('working_hours', task.working_hours)
            task.actual_work_content = data.get('actual_work_content', task.actual_work_content)
            task.remarks = data.get('remarks', task.remarks)

            task.update_time = datetime.utcnow()
            db.session.commit()

            return {
                "message": {
                    "result": 1,
                    "result_text": "更新任务成功",
                    "count": 1,
                    "data": [task.to_dict()]
                }
            }, 200

        except Exception as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": f"更新任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


# 删除
@ns_yth.route('/deleteItgtask/<int:id>')
class ItgtaskDeleteResource(Resource):

    @ns_yth.marshal_with(response_integrated_task_model)
    def delete(self, id):
        """删除一体化任务条目"""
        try:
            task = IntegratedTask.query.get(id)
            if task:
                db.session.delete(task)
                db.session.commit()

                response_data = {
                    "message": {
                        "result": 1,  # 删除成功
                        "result_text": "删除任务成功",
                        "count": 0,
                        "data": []
                    }
                }
                return response_data, 200  # 返回删除成功的状态码
            else:
                response_data = {
                    "message": {
                        "result": 0,  # 任务未找到
                        "result_text": "任务未找到",
                        "count": 0,
                        "data": []
                    }
                }
                return response_data, 404  # 返回未找到状态码

        except Exception as e:
            response_data = {
                "message": {
                    "result": 0,  # 发生异常
                    "result_text": f"删除任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 400  # 返回错误状态码


# 删除post
@ns_yth.route('/deleteItgtaskpost')
class ItgtaskDeletePostResource(Resource):
    @ns_yth.expect(kms_itg_task_del_model)
    @ns_yth.marshal_with(response_integrated_task_model)
    def post(self):
        """删除一体化任务条目post"""
        try:
            data = api.payload
            integrated_work_id = data.get('integrated_work_id')

            if not integrated_work_id:
                return {
                    "message": {
                        "result": 0,
                        "result_text": "缺少integrated_work_id",
                        "count": 0,
                        "data": []
                    }
                }, 400

            task = IntegratedTask.query.filter_by(integrated_work_id=integrated_work_id).first()
            if task:
                db.session.delete(task)
                db.session.commit()

                return {
                    "message": {
                        "result": 1,
                        "result_text": "删除任务成功",
                        "count": 0,
                        "data": []
                    }
                }, 200

            return {
                "message": {
                    "result": 0,
                    "result_text": "任务未找到",
                    "count": 0,
                    "data": []
                }
            }, 404

        except Exception as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": f"删除任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


class DuplicateDataError(Exception):
    pass


# integrated_task_file_attribute

@ns_yth.route('/getItgtaskfaadd')
class ItgtaskfaaddMapResource(Resource):
    @ns_yth.expect(kms_itg_task_fa_crud_model)
    @ns_yth.marshal_with(response_integrated_task_fa_model)
    def post(self):
        """新增一体化任务"""
        global existing_task
        try:
            # 获取请求的payload
            data = api.payload

            # 检查重复数据
            existing_task = IntegratedTaskFileAttribute.query.filter_by(
                integrated_work_id=data['integrated_work_id']
            ).first()
            if existing_task:
                raise DuplicateDataError("任务id数据已存在")

            # 检查重复数据
            existing_task = IntegratedTaskFileAttribute.query.filter_by(
                file_name=data['file_name']
            ).first()
            if existing_task:
                raise DuplicateDataError("任务名称数据已存在")

            # 创建数据库对象并保存
            new_task = IntegratedTaskFileAttribute(**data)
            db.session.add(new_task)
            db.session.commit()

            return format_response(
                result=1,
                result_text="新增成功",
                data=new_task.to_dict(),
                count=0,
            ), 201

        except DuplicateDataError as e:
            return format_response(
                result=0,
                result_text=str(e),
                data=existing_task.to_dict(),
                count=0
            ), 400
        except Exception as e:
            # 记录异常到日志
            app.logger.error(f"新增任务失败: {str(e)}")
            return format_response(
                result=0,
                result_text="新增任务失败",
                data=[],
                count=0
            ), 500


@ns_yth.route('/getItgtaskfafileadd')
class ItgtaskfafieladdMapResource(Resource):
    @jwt_required()
    @ns_yth.expect(kms_itg_task_fa_short_crud_model)
    @ns_yth.marshal_with(response_integrated_task_fa_model)
    def post(self):
        """新增一体化任务自动拼接文件名"""
        # # 检查权限
        # if not check_permission([9]):
        #     return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        global existing_task
        try:
            # 获取请求的 payload
            data = api.payload

            # 提取相关字段
            app_abbreviation = data.get('app_abbreviation')
            dev_department = data.get('dev_department')
            exchange_employee_id = data.get('exchange_employee_id')
            exchange_employee_name = data.get('exchange_employee_name')
            work_period = data.get('work_period')

            # 检查必填字段是否完整
            if not (
                    app_abbreviation and dev_department and exchange_employee_id and exchange_employee_name and work_period):
                raise ValueError("所有必填字段都需要提供")

            # 校验 work_period 格式（6 位：年月）
            if not (len(work_period) == 6 and work_period.isdigit()):
                raise ValueError("work_period 格式错误，必须为 6 位数字表示的年月")

            # 1. 根据年月（work_period）、应用、开发团队、开发人员生成文件名，各个属性用下划线拼接
            gen_file_name = f"{work_period}_{app_abbreviation}_{dev_department}_{exchange_employee_id}_{exchange_employee_name}"

            # 2. 获取与 work_period 相同的数据量
            current_period_count = IntegratedTaskFileAttribute.query.filter(
                IntegratedTaskFileAttribute.work_period == work_period
            ).count()

            print(current_period_count)

            # 3. 生成新的 `integrated_work_id` 基于当前日期（年月日）和数据量
            today = datetime.now().strftime("%Y%m%d")
            current_period_count += 1
            gen_integrated_work_id = int(f"{today}{work_period[4:]}{str(current_period_count).zfill(4)}")

            print(gen_integrated_work_id)
            # 检查是否存在重复的 `integrated_work_id`
            existing_task = IntegratedTaskFileAttribute.query.filter_by(
                integrated_work_id=gen_integrated_work_id
            ).first()
            if existing_task:
                raise DuplicateDataError("任务ID数据已存在")

            # 检查是否存在重复的 `file_name`
            existing_task = IntegratedTaskFileAttribute.query.filter_by(
                file_name=gen_file_name
            ).first()
            if existing_task:
                raise DuplicateDataError("任务文件名已存在")

            # 检查并设置时间字段
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            # 检查并设置时间字段
            data['create_time'] = data.get('create_time') or current_time
            data['update_time'] = data.get('update_time') or current_time

            # 将生成的字段加入数据字典
            data['integrated_work_id'] = gen_integrated_work_id
            data['file_name'] = gen_file_name

            # 创建数据库对象并保存
            new_task = IntegratedTaskFileAttribute(**data)
            db.session.add(new_task)
            db.session.commit()

            return {
                "message": {
                    "result": 1,
                    "result_text": "新增成功",
                    "count": 1,
                    "data": [new_task.to_dict()]
                }
            }, 201

        except DuplicateDataError as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": str(e),
                    "count": 0,
                    "data": [existing_task.to_dict()] if existing_task else []
                }
            }, 400
        except ValueError as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": str(e),
                    "count": 0,
                    "data": []
                }
            }, 400
        except Exception as e:
            # 记录异常到日志
            app.logger.error(f"新增任务失败: {str(e)}")
            return {
                "message": {
                    "result": 0,
                    "result_text": "新增任务失败",
                    "count": 0,
                    "data": []
                }
            }, 500


# 查询一体化任务post
@ns_yth.route('/getItgtaskfa')
class ItgtaskfaResource(Resource):
    @ns_yth.expect(query_itgfa_task_model)
    @ns_yth.marshal_with(response_integrated_task_fa_model)
    def post(self):
        """查询一体化任务post"""
        try:
            data = api.payload
            query_filters = []

            # Build query filters based on provided parameters
            if data.get('id'):
                query_filters.append(IntegratedTaskFileAttribute.id == data['id'])
            if data.get('integrated_work_id'):
                query_filters.append(IntegratedTaskFileAttribute.integrated_work_id == data['integrated_work_id'])
            if data.get('file_name'):
                query_filters.append(IntegratedTaskFileAttribute.file_name.like(f"%{data['file_name']}%"))
            if data.get('app_abbreviation'):
                query_filters.append(IntegratedTaskFileAttribute.app_abbreviation.like(f"%{data['app_abbreviation']}%"))
            if data.get('dev_department'):
                query_filters.append(IntegratedTaskFileAttribute.dev_department.like(f"%{data['dev_department']}%"))
            if data.get('exchange_employee_id'):
                query_filters.append(IntegratedTaskFileAttribute.exchange_employee_id == data['exchange_employee_id'])
            if data.get('exchange_employee_name'):
                query_filters.append(
                    IntegratedTaskFileAttribute.exchange_employee_name.like(f"%{data['exchange_employee_name']}%"))
            if data.get('work_period'):
                query_filters.append(IntegratedTaskFileAttribute.work_period == data['work_period'])

            # Execute query with filters
            query = IntegratedTaskFileAttribute.query
            if query_filters:
                query = query.filter(and_(*query_filters))
            results = query.all()

            return format_response(
                result=1,
                result_text="查询成功",
                data=[result.to_dict() for result in results],
                count=len(results)
            ), 200

        except Exception as e:
            app.logger.error(f"查询失败: {str(e)}")
            return format_response(
                result=0,
                result_text=f"查询失败: {str(e)}",
                data=[],
                count=0
            ), 500


@ns_yth.route('/updateItgtaskfa/<int:task_id>')
class UpdateItgtaskfaResource(Resource):
    @ns_yth.expect(kms_itg_task_fa_crud_model)
    @ns_yth.marshal_with(response_integrated_task_fa_model)
    def put(self, task_id):
        """修改一体化任务"""
        try:
            task = IntegratedTaskFileAttribute.query.get_or_404(task_id)

            # 获取原始数据
            original_integrated_work_id = task.integrated_work_id
            original_file_name = task.file_name

            # 使用 request.json 获取请求数据，更安全
            args = request.json

            # 检查新数据是否与其他记录重复（或关系）
            if (
                    IntegratedTaskFileAttribute.query
                            .filter(
                        (IntegratedTaskFileAttribute.integrated_work_id == args['integrated_work_id']) |
                        (IntegratedTaskFileAttribute.file_name == args['file_name'])
                    )
                            .filter(IntegratedTaskFileAttribute.id != task_id)  # 排除自身
                            .first()
            ):
                return format_response(
                    result=0,
                    result_text='修改失败：唯一标识或文件名已存在',
                    data=[],
                    count=0
                )

            # 更新数据
            for key, value in args.items():
                setattr(task, key, value)

            db.session.commit()
            return format_response(
                result=1,
                result_text='修改成功',
                data=task.to_dict(),
                count=1
            )
        except Exception as e:
            db.session.rollback()  # 回滚事务
            return format_response(
                result=0,
                result_text=f'修改失败: {str(e)}',
                data=[],
                count=0
            )


# 修改一体化任务post
@ns_yth.route('/updateItgtaskfa/')
class UpdateItgtaskfaResource(Resource):
    @jwt_required()
    @ns_yth.expect(kms_itg_task_fa_crud_model)
    @ns_yth.marshal_with(response_integrated_task_fa_model)
    def post(self):
        """修改一体化任务post"""
        # # 检查权限
        # if not check_permission([9]):
        #     return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        try:
            args = request.json
            integrated_work_id = int(args.get('integrated_work_id', 0))

            # 查找匹配的任务记录
            task = IntegratedTaskFileAttribute.query.filter(
                IntegratedTaskFileAttribute.integrated_work_id == integrated_work_id
            ).first()
            if not task:
                return format_response(
                    result=0,
                    result_text='修改失败：未找到匹配的记录',
                    data=[],
                    count=0
                )

            # 开始事务
            try:
                # 检查文件名是否重复
                if args.get('file_name') and args['file_name'] != task.file_name:
                    existing_record = IntegratedTaskFileAttribute.query.filter(
                        IntegratedTaskFileAttribute.file_name == args['file_name'],
                        IntegratedTaskFileAttribute.id != task.id
                    ).first()
                    if existing_record:
                        db.session.rollback()  # 回滚事务
                        return format_response(
                            result=0,
                            result_text='修改失败：文件名已存在于其他记录中',
                            data=[],
                            count=0
                        )

                # 更新字段（排除 'integrated_work_id' 和 'create_time'）
                for key, value in args.items():
                    if key != 'integrated_work_id' and key != 'create_time':
                        setattr(task, key, value)

                # 如果没有提供 'create_time'，则不更新它，'update_time' 更新为当前时间
                if 'create_time' not in args:
                    task.create_time = task.create_time  # 不更新 create_time
                if 'update_time' not in args:
                    task.update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 更新为当前时间

                db.session.commit()  # 提交事务

            except Exception as e:
                db.session.rollback()  # 回滚事务
                raise

            return format_response(
                result=1,
                result_text='修改成功',
                data=task.to_dict(),
                count=1
            )

        except Exception as e:
            app.logger.error(f"修改任务失败: {str(e)}")
            return format_response(
                result=0,
                result_text=f'修改失败: {str(e)}',
                data=[],
                count=0
            )


# 删除一体化任务post
@ns_yth.route('/delItgtaskfa/')
class DelItgtaskfaResource(Resource):
    @jwt_required()
    @ns_yth.expect(del_itgfa_task_model)
    @ns_yth.marshal_with(response_integrated_task_fa_model)
    def post(self):
        """删除一体化任务post"""

        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        try:
            # 使用 request.json 获取请求数据
            args = request.json

            # 验证 integrated_work_id 是否提供
            if not args.get('integrated_work_id'):
                return format_response(
                    result=0,
                    result_text='删除失败：未提供一体化工作表ID',
                    data=[],
                    count=0
                )

            # 根据 integrated_work_id 查找记录
            task = IntegratedTaskFileAttribute.query.filter(
                IntegratedTaskFileAttribute.integrated_work_id == args['integrated_work_id']
            ).first()

            if not task:
                return format_response(
                    result=0,
                    result_text='删除失败：未找到匹配的记录',
                    data=[],
                    count=0
                )

            # 保存要返回的数据
            deleted_task_data = task.to_dict()

            # 删除记录
            db.session.delete(task)
            db.session.commit()

            return format_response(
                result=1,
                result_text='删除成功',
                data=[deleted_task_data],  # 返回被删除的记录信息
                count=1
            )

        except Exception as e:
            db.session.rollback()  # 回滚事务
            return format_response(
                result=0,
                result_text=f'删除失败: {str(e)}',
                data=[],
                count=0
            )


@ns_yth.route('/itg_fa_page')
class ItgFaPageAddIdResource(Resource):
    @ns_yth.expect(itg_task_fa_qry_group_model)
    @ns_yth.marshal_with(response_itg_task_fa_data_model)
    def post(self):
        """一体化任务分页查询"""
        try:
            # 获取查询参数
            itgfadata = api.payload

            # 获取分页参数（使用模型中定义的默认值）
            page = itgfadata.get('pageNo', 1)
            per_page = itgfadata.get('pageSize', 20)
            max_page = itgfadata.get('max_page', 50)

            # 确保分页参数合法
            page = max(1, page)  # 页码最小值为1
            per_page = max(1, min(per_page, max_page))  # 每页条目数介于1和max_page之间

            # 构建查询条件
            query = db.session.query(IntegratedTaskFileAttribute)

            # 根据查询参数构造过滤条件，空数组不添加任何过滤
            if itgfadata.get('integrated_work_id'):
                if len(itgfadata['integrated_work_id']) > 0:
                    query = query.filter(
                        IntegratedTaskFileAttribute.integrated_work_id.in_(itgfadata['integrated_work_id']))
            if itgfadata.get('file_name'):
                if len(itgfadata['file_name']) > 0:
                    query = query.filter(IntegratedTaskFileAttribute.file_name.in_(itgfadata['file_name']))
            if itgfadata.get('app_abbreviation'):
                if len(itgfadata['app_abbreviation']) > 0:
                    query = query.filter(
                        IntegratedTaskFileAttribute.app_abbreviation.in_(itgfadata['app_abbreviation']))
            if itgfadata.get('dev_department'):
                if len(itgfadata['dev_department']) > 0:
                    query = query.filter(IntegratedTaskFileAttribute.dev_department.in_(itgfadata['dev_department']))
            if itgfadata.get('exchange_employee_id'):
                if len(itgfadata['exchange_employee_id']) > 0:
                    query = query.filter(
                        IntegratedTaskFileAttribute.exchange_employee_id.in_(itgfadata['exchange_employee_id']))
            if itgfadata.get('exchange_employee_name'):
                if len(itgfadata['exchange_employee_name']) > 0:
                    query = query.filter(
                        IntegratedTaskFileAttribute.exchange_employee_name.in_(itgfadata['exchange_employee_name']))
            if itgfadata.get('work_period'):
                if len(itgfadata['work_period']) > 0:
                    query = query.filter(IntegratedTaskFileAttribute.work_period.in_(itgfadata['work_period']))

            # 计算总记录数
            total_count = query.count()

            # 分页查询
            pagination = query.paginate(page=page, per_page=per_page, error_out=False)
            items = [item.to_dict() for item in pagination.items]

            # 构建响应数据
            response_data = {
                'has_next': pagination.has_next,
                'has_prev': pagination.has_prev,
                'data': items,
                'next': [item.to_dict() for item in
                         pagination.next(error_out=False).items] if pagination.has_next else [],
                'prev': [item.to_dict() for item in
                         pagination.prev(error_out=False).items] if pagination.has_prev else [],
                'pageNo': pagination.page,
                'totalPage': pagination.pages,
                'pageSize': pagination.per_page,
                'prev_num': pagination.prev_num,
                'next_num': pagination.next_num,
                'totalCount': total_count,
            }

            return format_response_page(
                message="查询成功",
                result=response_data,
                status=200,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        except Exception as e:
            # 错误处理
            return format_response_page(
                message=f"查询出错: {str(e)}",
                result=None,
                status=201,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )


# 对应关系
# work_category firstTitle
# work_subcategory secondTitle
# work_item taskItem
# work_task job
# working_hours time
# actual_work_content taskContent
# remarks comment
@ns_yth.route('/getItgtasklistpost')
class ItgtaskQueryListResource(Resource):
    @ns_yth.expect(list_itgfa_task_model)
    @ns_yth.marshal_with(response_integrated_task_list_model)
    def post(self):
        """查询一体化任务整文件post"""
        try:
            # 获取请求参数
            data = api.payload
            integrated_work_id_str = data.get('integrated_work_id')

            # 验证参数并转换类型
            if not integrated_work_id_str:
                raise ValueError("一体化工作表ID不能为空")
            try:
                integrated_work_id = int(integrated_work_id_str)
            except ValueError:
                raise ValueError("一体化工作表ID必须为整数")

            # 查询 IntegratedTaskFileAttribute 表
            file_attributes = db.session.query(
                IntegratedTaskFileAttribute.integrated_work_id,
                IntegratedTaskFileAttribute.file_name
            ).filter(IntegratedTaskFileAttribute.integrated_work_id == integrated_work_id).all()

            # 如果没有匹配的记录，直接返回空结果
            if not file_attributes:
                return {
                    "message": {
                        "result": 1,
                        "result_text": "未查询到任务文件属性",
                        "count": 0,
                        "data": []
                    }
                }, 200

            # 查询 IntegratedTask 表的数据
            task_records = db.session.query(IntegratedTask).filter(
                IntegratedTask.integrated_work_id == integrated_work_id
            ).all()

            # 构造响应数据
            data_list = []
            task_dict = {}

            for task in task_records:
                first_title = task.work_category
                second_title = task.work_subcategory
                content_entry = {
                    'taskItem': task.work_item,
                    'job': task.work_task,
                    'time': task.working_hours,
                    'taskContent': task.actual_work_content,
                    'comment': task.remarks,
                }

                if first_title not in task_dict:
                    task_dict[first_title] = {}

                if second_title not in task_dict[first_title]:
                    task_dict[first_title][second_title] = []

                task_dict[first_title][second_title].append(content_entry)

            for first_title, second_dict in task_dict.items():
                task_data = {
                    "firstTitle": first_title,
                    "data": []
                }
                for second_title, content_list in second_dict.items():
                    task_data["data"].append({
                        "secondTitle": second_title,
                        "content": content_list
                    })
                data_list.append(task_data)

            # 返回结果
            return {
                "message": {
                    "result": file_attributes[0].integrated_work_id,
                    "result_text": file_attributes[0].file_name,
                    "count": len(task_records),
                    "data": data_list
                }
            }, 200

        except Exception as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": f"查询任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


# 查询一体化任务整文件post data为非数组
@ns_yth.route('/getItgtasklistnoarraypost')
class ItgtaskQueryListnoarrayResource(Resource):
    @ns_yth.expect(list_itgfa_task_model)
    @ns_yth.marshal_with(response_integrated_task_list_noarray_model)
    def post(self):
        """查询一体化任务整文件post data为非数组"""
        try:
            # 获取请求参数
            data = api.payload
            integrated_work_id_str = data.get('integrated_work_id')

            # 验证参数并转换类型
            if not integrated_work_id_str:
                raise ValueError("一体化工作表ID不能为空")
            try:
                integrated_work_id = int(integrated_work_id_str)
            except ValueError:
                raise ValueError("一体化工作表ID必须为整数")

            # 查询 IntegratedTask 表的数据
            task_records = db.session.query(IntegratedTask).filter(
                IntegratedTask.integrated_work_id == integrated_work_id
            ).all()

            # 如果没有匹配的记录，直接返回空结果
            if not task_records:
                return {
                    "message": {
                        "result": integrated_work_id,
                        "result_text": "未查询到任务文件属性",
                        "count": 0,
                        "data": []
                    }
                }, 200

            # 按 firstTitle 和 secondTitle 分组整合数据
            grouped_data = {}
            for task in task_records:
                first_title = task.work_category
                second_title = task.work_subcategory
                content_entry = {
                    'id': str(task.id),  # 添加任务的 ID
                    'taskItem': task.work_item,
                    'job': task.work_task,
                    'time': task.working_hours,
                    'taskContent': task.actual_work_content,
                    'comment': task.remarks,
                }

                if first_title not in grouped_data:
                    grouped_data[first_title] = {}

                if second_title not in grouped_data[first_title]:
                    grouped_data[first_title][second_title] = []

                grouped_data[first_title][second_title].append(content_entry)

            # 构造返回结果
            data_list = []
            for first_title, second_title_map in grouped_data.items():
                for second_title, contents in second_title_map.items():
                    data_list.append({
                        "firstTitle": first_title,
                        "data": {
                            "secondTitle": second_title,
                            "content": contents
                        }
                    })

            # 返回结果
            return {
                "message": {
                    "result": integrated_work_id,
                    "result_text": "查询成功",
                    "count": len(task_records),
                    "data": data_list
                }
            }, 200

        except Exception as e:
            return {
                "message": {
                    "result": 0,
                    "result_text": f"查询任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


# 更新一体化任务整文件post data为非数组
@ns_yth.route('/UpdItgtasklistnoarrayupdpost')
class ItgtaskUpdUpdnoarrayResource(Resource):
    @jwt_required()
    @ns_yth.expect(list_itgfa_upd_task_model)
    @ns_yth.marshal_with(response_integrated_task_list_noarray_model)
    def post(self):
        """更新一体化任务整文件post data为非数组"""
        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        try:
            # 获取请求参数
            data = api.payload
            integrated_work_id_str = data.get('integrated_work_id')
            integrated_work_op = data.get('integrated_work_op')
            integrated_work_data = data.get('integrated_work_data')

            # 验证参数并转换类型
            if not integrated_work_id_str:
                raise ValueError("一体化工作表ID不能为空")
            try:
                integrated_work_id = int(integrated_work_id_str)
            except ValueError:
                raise ValueError("一体化工作表ID必须为整数")

            # 查询 IntegratedTask 表的数据
            task_records_to_compare = db.session.query(IntegratedTask).filter(
                IntegratedTask.integrated_work_id == integrated_work_id
            ).all()

            # 将 task_records_to_compare 转换为与 integrated_work_data 相同的格式
            existing_data = {}
            for task in task_records_to_compare:
                first_title = task.work_category
                second_title = task.work_subcategory
                content_entry = {
                    'id': str(task.id),  # 添加任务的 ID
                    'taskItem': task.work_item,
                    'job': task.work_task,
                    'time': task.working_hours,
                    'taskContent': task.actual_work_content,
                    'comment': task.remarks,
                }

                if first_title not in existing_data:
                    existing_data[first_title] = {}

                if second_title not in existing_data[first_title]:
                    existing_data[first_title][second_title] = []

                existing_data[first_title][second_title].append(content_entry)

            # 比较 integrated_work_data 与 existing_data
            for task_data in integrated_work_data:  # integrated_work_data 是一个列表
                first_title = task_data.get('firstTitle')
                data_entry = task_data.get('data')
                second_title = data_entry.get('secondTitle')
                contents = data_entry.get('content')

                # 检查是否存在对应的 firstTitle 和 secondTitle
                if first_title not in existing_data or second_title not in existing_data[first_title]:
                    # 如果不存在，插入新数据
                    for content in contents:
                        new_task = IntegratedTask(
                            integrated_work_id=integrated_work_id,
                            work_category=first_title,
                            work_subcategory=second_title,
                            work_item=content['taskItem'],
                            work_task=content['job'],
                            working_hours=content['time'],
                            actual_work_content=content['taskContent'],
                            remarks=content.get('comment', '')
                        )
                        db.session.add(new_task)
                else:
                    # 如果存在，更新现有数据
                    existing_contents = existing_data[first_title][second_title]
                    for content in contents:
                        # 查找匹配的 existing_content
                        matched_content = None
                        for existing_content in existing_contents:
                            if existing_content['id'] == content.get('id'):
                                matched_content = existing_content
                                break

                        if matched_content:
                            # 更新现有记录
                            task_to_update = db.session.query(IntegratedTask).filter(
                                IntegratedTask.id == int(matched_content['id'])
                            ).first()
                            if task_to_update:
                                task_to_update.work_item = content['taskItem']
                                task_to_update.work_task = content['job']
                                task_to_update.working_hours = content['time']
                                task_to_update.actual_work_content = content['taskContent']
                                task_to_update.remarks = content.get('comment', '')
                        else:
                            # 插入新记录
                            new_task = IntegratedTask(
                                integrated_work_id=integrated_work_id,
                                work_category=first_title,
                                work_subcategory=second_title,
                                work_item=content['taskItem'],
                                work_task=content['job'],
                                working_hours=content['time'],
                                actual_work_content=content['taskContent'],
                                remarks=content.get('comment', '')
                            )
                            db.session.add(new_task)

            # 删除 existing_data 中存在但 integrated_work_data 中不存在的数据
            # 收集 integrated_work_data 中的所有 id
            integrated_work_data_ids = set()
            for task_data in integrated_work_data:
                data_entry = task_data.get('data', {})
                contents = data_entry.get('content', [])
                for content in contents:
                    if 'id' in content:
                        integrated_work_data_ids.add(content['id'])

            # 遍历 existing_data，删除 integrated_work_data 中不存在的记录
            for first_title, second_title_map in existing_data.items():
                for second_title, contents in second_title_map.items():
                    for content in contents:
                        if content['id'] not in integrated_work_data_ids:
                            # 删除对应的记录
                            task_to_delete = db.session.query(IntegratedTask).filter(
                                IntegratedTask.id == int(content['id'])
                            ).first()
                            if task_to_delete:
                                db.session.delete(task_to_delete)

            # 提交数据库更改
            db.session.commit()

            # 更新完后查询返回
            task_records = db.session.query(IntegratedTask).filter(
                IntegratedTask.integrated_work_id == integrated_work_id
            ).all()

            # 如果没有匹配的记录，直接返回空结果
            if not task_records:
                return {
                    "message": {
                        "result": integrated_work_id,
                        "result_text": "未查询到任务文件属性",
                        "count": 0,
                        "data": []
                    }
                }, 200

            # 按 firstTitle 和 secondTitle 分组整合数据
            grouped_data = {}
            for task in task_records:
                first_title = task.work_category
                second_title = task.work_subcategory
                content_entry = {
                    'id': str(task.id),  # 添加任务的 ID
                    'taskItem': task.work_item,
                    'job': task.work_task,
                    'time': task.working_hours,
                    'taskContent': task.actual_work_content,
                    'comment': task.remarks,
                }

                if first_title not in grouped_data:
                    grouped_data[first_title] = {}

                if second_title not in grouped_data[first_title]:
                    grouped_data[first_title][second_title] = []

                grouped_data[first_title][second_title].append(content_entry)

            # 构造返回结果
            data_list = []
            for first_title, second_title_map in grouped_data.items():
                for second_title, contents in second_title_map.items():
                    data_list.append({
                        "firstTitle": first_title,
                        "data": {
                            "secondTitle": second_title,
                            "content": contents
                        }
                    })

            # 返回结果
            return {
                "message": {
                    "result": integrated_work_id,
                    "result_text": "更新成功",
                    "count": len(task_records),
                    "data": data_list
                }
            }, 200

        except Exception as e:
            db.session.rollback()  # 发生异常时回滚
            return {
                "message": {
                    "result": 0,
                    "result_text": f"更新任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


# 清空(初始化)一体化任务整文件post data为非数组
@ns_yth.route('/delItgtasklistnoarraypost')
class ItgtaskdelListnoarrayResource(Resource):
    @jwt_required()
    @ns_yth.expect(list_itgfa_task_del_model)
    @ns_yth.marshal_with(response_integrated_task_list_noarray_model)
    def post(self):
        """清空(初始化)一体化任务整文件post data为非数组"""

        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        try:
            # 获取请求参数
            data = api.payload
            integrated_work_id_str = data.get('integrated_work_id')

            # 验证参数并转换类型
            if not integrated_work_id_str:
                raise ValueError("一体化工作表ID不能为空")
            try:
                integrated_work_id = int(integrated_work_id_str)
            except ValueError:
                raise ValueError("一体化工作表ID必须为整数")

            # 查询 IntegratedTask 表的数据
            task_records = db.session.query(IntegratedTask).filter(
                IntegratedTask.integrated_work_id == integrated_work_id
            ).all()

            # 如果没有匹配的记录，直接返回空结果
            if not task_records:
                return {
                    "message": {
                        "result": integrated_work_id,
                        "result_text": "未查询到任务文件属性",
                        "count": 0,
                        "data": []
                    }
                }, 200

            # 按 firstTitle 和 secondTitle 分组整合数据
            grouped_data = {}
            for task in task_records:
                first_title = task.work_category
                second_title = task.work_subcategory
                content_entry = {
                    'id': str(task.id),  # 添加任务的 ID
                    'taskItem': task.work_item,
                    'job': task.work_task,
                    'time': task.working_hours,
                    'taskContent': task.actual_work_content,
                    'comment': task.remarks,
                }

                if first_title not in grouped_data:
                    grouped_data[first_title] = {}

                if second_title not in grouped_data[first_title]:
                    grouped_data[first_title][second_title] = []

                grouped_data[first_title][second_title].append(content_entry)

            # 构造返回结果
            data_list = []
            for first_title, second_title_map in grouped_data.items():
                for second_title, contents in second_title_map.items():
                    data_list.append({
                        "firstTitle": first_title,
                        "data": {
                            "secondTitle": second_title,
                            "content": contents
                        }
                    })

            # 删除(清空或者初始化)这些数据
            db.session.query(IntegratedTask).filter(
                IntegratedTask.integrated_work_id == integrated_work_id
            ).delete()
            db.session.commit()

            # 返回结果
            return {
                "message": {
                    "result": integrated_work_id,
                    "result_text": "清空（初始化）成功",
                    "count": len(task_records),
                    "data": data_list
                }
            }, 200

        except Exception as e:
            db.session.rollback()  # 发生异常时回滚
            return {
                "message": {
                    "result": 0,
                    "result_text": f"清空（初始化）任务失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }, 400


# 查询一体化配置post
@ns_yth.route('/getItgtaskconfig')
class ItgtaskconfigResource(Resource):
    @ns_yth.expect(itg_task_config_qry_model)
    @ns_yth.marshal_with(response_integrated_task_config_model)
    # @jwt_required()  # 如果需要 JWT 认证
    def post(self):
        """查询一体化配置post"""
        try:
            data = request.json  # 获取请求体中的 JSON 数据
            query_filters = []

            # 根据传入的参数动态构建查询过滤器
            if 'work_category' in data and data['work_category']:
                query_filters.append(IntegratedTaskConfig.work_category.in_(data['work_category']))
            if 'work_subcategory' in data and data['work_subcategory']:
                query_filters.append(IntegratedTaskConfig.work_subcategory.in_(data['work_subcategory']))
            if 'work_item' in data and data['work_item']:
                query_filters.append(IntegratedTaskConfig.work_item.in_(data['work_item']))

            # 构建查询
            query = IntegratedTaskConfig.query
            if query_filters:
                query = query.filter(and_(*query_filters))

            # 执行查询
            results = query.all()

            # 返回格式化响应
            return format_response(
                result=1,
                result_text="查询成功",
                data=[result.to_dict() for result in results],
                count=len(results)
            ), 200

        except Exception as e:
            app.logger.error(f"查询失败: {str(e)}")
            return format_response(
                result=0,
                result_text=f"查询失败: {str(e)}",
                data=[],
                count=0
            ), 500


# 定义上传 API
@ns_yth.route('/ythupload')
class AlarmRptUploadResource(Resource):
    @jwt_required()
    @ns_yth.expect(yth_upload_model)  # 指定期望的请求模型
    @ns_yth.marshal_with(response_yth_upload_model)
    def post(self):
        """上传一体化任务文件"""

        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        try:
            logging.info("开始处理文件上传...")
            # print(request.headers)

            # print(request.files)  # 打印请求文件

            # 直接从 request.files 中提取上传的文件
            file = request.files.get('file')

            print(file.filename)

            if not file or file.filename == '':
                raise ValueError("未选择文件")

            # 获取文件名
            original_filename = file.filename

            # 保存文件到上传目录
            original_file_path = os.path.join(YTH_UPLOAD_FOLDER, original_filename)
            file.save(original_file_path)

            # 获取文件后缀名
            file_ext = os.path.splitext(original_filename)[1]
            timestamp = datetime.now().strftime('%Y%m%d%H%M%S')
            copied_filename = f"{os.path.splitext(original_filename)[0]}_{timestamp}{file_ext}"
            copied_file_path = os.path.join(YTH_UPLOAD_COPY_FOLDER, copied_filename)

            # 复制文件
            shutil.copy(original_file_path, copied_file_path)

            # 构建下载 URL ，确保有一个 @ns_yth.route('/download/<filename>') 的路由
            download_url = url_for('ns_yth_file_download', filename=copied_filename, _external=True)

            # 构建响应数据
            response_data = {
                "message": {
                    "result": 1,
                    "result_text": "文件上传并复制成功",
                    "data": [
                        {
                            'original_file_path': original_file_path,
                            'copied_file_path': copied_file_path,
                            'download_url': download_url
                        }
                    ]
                }
            }
            return response_data, 200

        except Exception as e:
            logging.error(f"文件上传失败: {str(e)}")
            return {
                "message": {
                    "result": 0,
                    "result_text": f"文件上传失败: {str(e)}",
                    "data": []
                }
            }, 500


@ns_yth.route('/ythuploadlist')
class YthFileUpList(Resource):
    @ns_yth.expect(yth_up_file_list_query_model)
    @ns_yth.marshal_with(response_yth_up_file_list_model)
    def post(self):
        """一体化文件列表"""
        try:
            # 获取请求的目录名称
            # dir_name = api.payload.get('dir_name')
            dir_name = api.payload.get('dir_name', YTH_UPLOAD_COPY_FOLDER)  # 如果 dir_name 未上送，则使用默认值

            # 拼接绝对路径
            base_dir = os.path.abspath(dir_name)
            print(dir_name)
            if not os.path.exists(base_dir) or not os.path.isdir(base_dir):
                raise ValueError(f"指定目录 {dir_name} 不存在")

            # 查询目录下的所有文件
            file_list = []
            for file_name in os.listdir(base_dir):
                file_path = os.path.join(base_dir, file_name)

                # 确保只处理文件（忽略子目录）
                if os.path.isfile(file_path):
                    # 获取文件的详细信息
                    file_ctime = datetime.fromtimestamp(os.path.getctime(file_path)).strftime('%Y-%m-%d %H:%M:%S')
                    file_size = f"{os.path.getsize(file_path)} bytes"
                    file_type = os.path.splitext(file_name)[1][1:] if '.' in file_name else "Unknown"

                    # 构建单个文件的信息
                    file_list.append({
                        'file_name': file_name,
                        'file_ctime': file_ctime,
                        'file_type': file_type,
                        'file_size': file_size
                    })

            # 构建返回数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "文件列表查询成功",
                    "count": len(file_list),
                    "data": file_list
                }
            }
            return response_data, 200

        except Exception as e:
            # 异常处理
            response_data = {
                "message": {
                    "result": 0,  # 上传失败
                    "result_text": f"文件查询失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 500


# 文件下载 API
@ns_yth.route('/download/<filename>')
class FileDownload(Resource):
    def get(self, filename):
        """从上传目录中下载文件"""
        try:
            # 确保文件存在于上传目录中
            file_path = os.path.join(YTH_UPLOAD_COPY_FOLDER, filename)
            print(file_path)
            print(os.curdir)
            print(os.path.exists(file_path))
            if not os.path.exists(file_path):
                response_data = {
                    "message": {
                        "result": 0,
                        "result_text": "文件未找到",
                        "data": []
                    }
                }
                return response_data, 404

            # send找不到UPLOAD_COPY_FOLDER 路径，原因不详，需要切换成 DOWNLOAD_FOLDER
            file_path_download = os.path.join(YTH_DOWNLOAD_FOLDER, filename)

            # 发送文件作为下载
            basename = os.path.basename(filename)
            print(basename)
            rt = send_file(str(file_path_download))
            # 解决不支持中文的问题
            rt.headers["Content-Disposition"] = \
                "attachment;" \
                "filename*=UTF-8''{utf_filename}".format(
                    utf_filename=quote(basename.encode('utf-8'))
                )
            return rt

        except Exception as e:
            # 捕获异常并返回错误信息
            response_data = {
                "message": {
                    "result": 0,  # 下载失败
                    "result_text": f"文件下载过程中发生错误: {str(e)}",
                    "data": []
                }
            }
            return response_data, 500


# 列头
# 工作类别	工作细类	工作项	工作任务	工时	实际工作内容	备注

# 对应关系
# work_category 对应 firstTitle , 对应  工作类别
# work_subcategory 对应 secondTitle , 对应 工作细类
# work_item 对应 taskItem , 对应 工作项
# work_task 对应 job , 对应 工作任务
# working_hours 对应 time, 对应  工时
# actual_work_content 对应 taskContent , 对应  实际工作内容
# remarks 对应 comment , 对应 备注

# 解析excel并返回数据
def fill_merged_cells(file_path, sheet_name=None):
    """填充合并单元格的值"""
    if file_path.endswith('.xlsx'):
        # 处理 .xlsx 文件
        workbook = load_workbook(filename=file_path, data_only=True)
        sheet = workbook.active if sheet_name is None else workbook[sheet_name]
        merged_cells = sheet.merged_cells.ranges

        # 创建一个字典，存储合并单元格的值
        merged_cells_dict = {}
        for merged_cell in merged_cells:
            min_row, min_col, max_row, max_col = (
                merged_cell.min_row, merged_cell.min_col,
                merged_cell.max_row, merged_cell.max_col
            )
            cell_value = sheet.cell(row=min_row, column=min_col).value
            # 将 openpyxl 的 1-based 索引转换为 pandas 的 0-based 索引
            for row in range(min_row - 1, max_row):  # 转换为 0-based
                for col in range(min_col - 1, max_col):  # 转换为 0-based
                    merged_cells_dict[(row, col)] = cell_value
    else:
        # 处理 .xls 文件
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0) if sheet_name is None else workbook.sheet_by_name(sheet_name)
        merged_cells = sheet.merged_cells

        # 创建一个字典，存储合并单元格的值
        merged_cells_dict = {}
        for crange in merged_cells:
            rlo, rhi, clo, chi = crange
            cell_value = sheet.cell_value(rlo, clo)
            # xlrd 的索引已经是 0-based，无需转换
            for row in range(rlo, rhi):
                for col in range(clo, chi):
                    merged_cells_dict[(row, col)] = cell_value

    return merged_cells_dict


def parse_excel(file_path):
    """解析Excel文件"""
    # 读取Excel文件
    if file_path.endswith('.xlsx'):
        engine = 'openpyxl'
    elif file_path.endswith('.xls'):
        engine = 'xlrd'
    else:
        raise ValueError("Unsupported file format. Please upload an .xlsx or .xls file.")

    # 获取第一个工作表的名称
    if file_path.endswith('.xlsx'):
        workbook = load_workbook(filename=file_path, data_only=True)
        sheet_name = workbook.sheetnames[0]  # 获取第一个工作表的名称
    else:
        workbook = xlrd.open_workbook(file_path)
        sheet_name = workbook.sheet_names()[0]  # 获取第一个工作表的名称

    # 读取 Excel 文件，确保表头是第一行
    df = pd.read_excel(file_path, engine=engine, header=0, sheet_name=sheet_name)

    # 检查列名是否正确
    expected_columns = ['工作类别', '工作细类', '工作项', '工作任务', '工时', '实际工作内容', '备注']
    if not all(col in df.columns for col in expected_columns):
        raise ValueError("Excel 文件的列名不符合预期，请检查文件格式。")

    # 将列的数据类型转换为 object（字符串类型）
    df = df.astype('object')

    # 填充合并单元格的值
    merged_cells_dict = fill_merged_cells(file_path, sheet_name)

    # print(merged_cells_dict)
    for (row_idx, col_idx), value in merged_cells_dict.items():
        # 检查行索引和列索引是否在 DataFrame 的范围内
        if row_idx - 1 < len(df) and col_idx - 1 < len(df.columns):
            df.iat[row_idx - 1, col_idx - 1] = value  # Excel索引从1开始，DataFrame索引从0开始

    # 动态填充 firstTitle 和 secondTitle
    first_title = None
    second_title = None
    for index, row in df.iterrows():

        # 获取当前行的第一列和第二列的值
        current_first_title = row['工作类别']

        current_second_title = row['工作细类']

        # 如果当前行的第一列有值，则更新 firstTitle
        if not pd.isna(current_first_title):
            first_title = current_first_title

        # 如果当前行的第二列有值，则更新 secondTitle
        if not pd.isna(current_second_title):
            second_title = current_second_title

        # 将 firstTitle 和 secondTitle 填充到当前行
        df.at[index, '工作类别'] = first_title
        df.at[index, '工作细类'] = second_title

    # 解析数据
    parsed_data = defaultdict(lambda: defaultdict(list))
    for index, row in df.iterrows():
        first_title = row['工作类别']
        second_title = row['工作细类']

        task_item = row['工作项'] if pd.notnull(row['工作项']) else ""
        job = row['工作任务'] if pd.notnull(row['工作任务']) else ""
        time = str(row['工时']) if pd.notnull(row['工时']) else ""
        task_content = row['实际工作内容'] if pd.notnull(row['实际工作内容']) else ""
        comment = row['备注'] if pd.notnull(row['备注']) else ""

        task_content_dict = {
            'id': 0,  #
            'taskItem': task_item,
            'job': job,
            'time': time,
            'taskContent': task_content,
            'comment': comment
        }

        parsed_data[first_title][second_title].append(task_content_dict)

    final_parsed_data = [
        {
            'firstTitle': first_title,
            'data': {
                'secondTitle': second_title,
                'content': tasks
            }
        }
        for first_title, second_titles in parsed_data.items()
        for second_title, tasks in second_titles.items()
    ]

    return final_parsed_data


@ns_yth.route('/ythparseupexcel')
class YthParseUpExcel(Resource):
    @jwt_required()
    @ns_yth.expect(yth_parse_dir_file_model)
    @ns_yth.marshal_with(response_integrated_task_list_noarray_model)
    def post(self):
        """解析上传的一体化 Excel 数据"""

        # 检查角色
        # 2 为一体化用户
        if not check_simple_roles(YTH_ROLES):
            return {'message': {'result': 403, 'result_text': 'Permission denied', 'count': 0, 'data': []}}, 403

        try:
            # 获取上传的目录和文件名
            dir_name = ns_yth.payload.get('dir_name', YTH_UPLOAD_COPY_FOLDER)  # 如果 dir_name 未上送，则使用默认值
            excel_name = ns_yth.payload['excel_name']
            file_path = os.path.join(dir_name, excel_name)

            logging.debug(f"Reading file from: {file_path}")

            if not os.path.exists(file_path):
                raise FileNotFoundError(f"File {file_path} does not exist.")

            # 检查文件是否为空
            if os.path.getsize(file_path) == 0:
                raise ValueError("The file is empty.")

            # 解析 Excel 文件
            parsed_data = parse_excel(file_path)

            response_data = {
                "message": {
                    "result": 1,  # 上传成功
                    "result_text": "文件解析成功",
                    "count": len(parsed_data),
                    "data": parsed_data
                }
            }

            return response_data, 200

        except Exception as e:
            # 异常处理
            response_data = {
                "message": {
                    "result": 0,  # 上传失败
                    "result_text": f"解析文件失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 500



